
/* Drop Triggers */

DROP TRIGGER TRI_worklog_workseq;



/* Drop Tables */

DROP TABLE worklog CASCADE CONSTRAINTS;
DROP TABLE employee CASCADE CONSTRAINTS;



/* Drop Sequences */

DROP SEQUENCE SEQ_worklog_workseq;




/* Create Sequences */

CREATE SEQUENCE SEQ_worklog_workseq INCREMENT BY 1 START WITH 1;



/* Create Tables */

CREATE TABLE employee
(
	id varchar2(20) NOT NULL,
	name varchar2(20),
	pass varchar2(20),
	PRIMARY KEY (id)
);


CREATE TABLE worklog
(
	workseq number(10,0) NOT NULL,
	stamp timestamp DEFAULT systimestamp,
	work varchar2(100),
	id varchar2(20) NOT NULL,
	PRIMARY KEY (workseq)
);



/* Create Foreign Keys */

ALTER TABLE worklog
	ADD FOREIGN KEY (id)
	REFERENCES employee (id)
;



/* Create Triggers */

CREATE OR REPLACE TRIGGER TRI_worklog_workseq BEFORE INSERT ON worklog
FOR EACH ROW
BEGIN
	SELECT SEQ_worklog_workseq.nextval
	INTO :new.workseq
	FROM dual;
END;

/




